/*** 
This do-file compares the changes in average daily spending across industries in the
Affinity Solutions credit and debit card data to the Advance Monthly Retail 
Trade Survey (MARTS).
***/

*-------------------------------------------------------
* Set up
*-------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Spending"

* Create required subfolders
cap mkdir "${root}/results/Spending"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/`category'"

*-------------------------------------------------------
* Load MARTS data
*-------------------------------------------------------

* Load MARTS data, non-seasonally-adjusted
project, uses("${root}/data/derived/MARTS/MARTS monthly sales by industry.dta")
use if is_adj==0 using "${root}/data/derived/MARTS/MARTS monthly sales by industry.dta", clear
drop is_adj

* Reshape wide on selected industries
isid month cat_code
keep if inlist(cat_code, "444", "445", "448", "451", "452")

drop cat_desc
rename sales sales_
reshape wide sales_, i(month) j(cat_code) string

label var sales_444 "Monthly sales, in $MLN. 444: Building Mat. and Garden Equip. and Supplies Dealers"
rename sales_444 sales_hic
label var sales_445 "Monthly sales, in $MLN. 445: Food and Beverage Stores"
rename sales_445 sales_grf
label var sales_448 "Monthly sales, in $MLN. 448: Clothing and Clothing Access. Stores"
rename sales_448 sales_aap
label var sales_451 "Monthly sales, in $MLN. 451: Sporting Goods, Hobby, Musical Instrument, Book Stores"
rename sales_451 sales_sgh
label var sales_452 "Monthly sales, in $MLN. 452: General Merchandise Stores"
rename sales_452 sales_gen

* Compute daily sales
gen byte days_in_month = dofm(month + 1) - dofm(month)
foreach var of varlist sales_* {
	local ind = subinstr("`var'", "sales_", "", 1)
	
	gen double dailysales_`ind' = `var' / days_in_month
}
drop days_in_month sales_*

* Single norm 2019 to January 2019, 2020 onward to January 2020
keep if year(dofm(month))>=2019
sort month
assert month[1] == ym(2019, 1)
assert month[13] == ym(2020, 1)
foreach var of varlist dailysales_* {
	local ind = subinstr("`var'", "dailysales_", "", 1)
	
	gen double singlenorm_`ind' = cond(year(dofm(month)) == 2019, (`var' / `var'[1]), (`var' / `var'[13]))
}

* Double norm
sort month
assert month[1] == ym(2019, 1)
assert month[12] == ym(2019, 12)
foreach var of varlist singlenorm_* {
	local ind = subinstr("`var'", "singlenorm_", "", 1)
	gen doublenorm_`ind' = `var' / `var'[month(dofm(month))] - 1 if year(dofm(month)) >= 2020
}

* Output
keep month doublenorm_* dailysales_*
keep if year(dofm(month)) >= 2020
rename doublenorm_* marts_*
rename dailysales_* marts_dailysales_*

isid month
tempfile marts
save `marts'

*-------------------------------------------------------------------------------
* Load Affinity data
*-------------------------------------------------------------------------------

* Load National Affinity data published on the Economic Tracker
project, uses("${root}/data/web/data/Affinity - National - Daily.csv")
import delimited "${root}/data/web/data/Affinity - National - Daily.csv", asdouble clear

* Keep relevant variables
keep year month day spend_aap spend_sgh spend_gen spend_hic spend_grf

* Compute average within each month
isid year month day
collapse (mean) spend*, by(year month) fast

rename spend_* affinity_*

* Create Stata month variable
gen ym = ym(year, month)
format %tm ym
drop year month
order ym
rename ym month

* Drop last month of data: likely incomplete
isid month
sort month
drop in -1

*-------------------------------------------------------------------------------
* Make Plot
*-------------------------------------------------------------------------------

* Merge MARTS with Affinity
merge 1:1 month using `marts', nogen

* Generate vars containing January 2020 daily sales from MARTS
keep if year(dofm(month))>=2020
sort month
assert month[1]==ym(2020, 1)

foreach var of varlist marts_dailysales_* {
	local ind = subinstr("`var'", "marts_dailysales_", "", 1)
	gen jan2020sales_`ind' = `var'[1]
}
drop marts_dailysales_*

* Restrict to desired month
keep if year(dofm(month)) == 2020 & month(dofm(month)) == 4

* Calculate correlation between Affinity and MARTS
reshape long affinity marts jan2020sales, i(month) j(industry) string

reg affinity marts
scalar _combined_coef = round(_b[marts] * 100, 0.01)

corr affinity marts [w = jan2020sales]
scalar _combined_corr = round(r(rho), 0.01)
local _combined_corr : di %3.2f scalar(_combined_corr)
di "`_combined_corr'"

reshape wide
order month affinity_* marts_* jan2020sales_*

* Create scatter
gen label_aap = "{space 15}Apparel and Accessories"
gen label_sgh = "Sporting Goods and Hobby"
gen label_gen = "General Merchandise Stores"
gen label_hic = "Home Improvement Centers"
gen label_grf = "Grocery and Food Stores"

tw	(function y=x, range(-1 0.5) col(oi2)) ///
	(scatter affinity_gen marts_gen, mlabel(label_gen) mlabsize(medsmall) col(oi1) mlabcol(oi1)) ///
	(scatter affinity_hic marts_hic, mlabel(label_hic) mlabsize(medsmall) col(oi1) mlabcol(oi1) mlabpos(11)) ///
	(scatter affinity_aap marts_aap, mlabel(label_aap) mlabsize(medsmall) col(oi1) mlabcol(oi1) mlabpos(12)) ///
	(scatter affinity_sgh marts_sgh, mlabel(label_sgh) mlabsize(medsmall) col(oi1) mlabcol(oi1) mlabpos(11)) ///
	(scatter affinity_grf marts_grf, mlabel(label_grf) mlabsize(medsmall) col(oi1) mlabcol(oi1) mlabpos(4)), ///
	legend(off) ///
	ylab(-1 "-100%" -0.50 "-50%" 0 "0%" 0.50 "+50%", nogrid) ///
	xlab(-1 "-100%" -0.50 "-50%" 0 "0%" 0.50 "+50%", nogrid) ///
	ytitle("Change in Affinity Spending (%)" "from January to April 2020") ///
	xtitle("Change in Advance Monthly Retail Trade Survey Revenue (%)" "from January to April 2020") ///
	text(-0.95 0.34 "Correlation: `_combined_corr'", col(gs8) size(medsmall)) ///
	graphr(margin(r+4)) ///
	${title_`version'}
oi_graph_export "${root}/results/Spending/Spending by Industry in Affinity vs MRTS", type(${fig_type})

*-------------------------------------------------------------------------------
* Export output numbers to csv file
*-------------------------------------------------------------------------------

cap erase "${root}/results/paper numbers/`category'/Affinity Solutions Data vs MRTS by Industry.yaml"

yamlout using "${root}/results/paper numbers/`category'/Affinity Solutions Data vs MRTS by Industry.yaml", ///
	key("aff_mrts_ind_corr") ///
	comment("Correlation of Affinity vs MARTS by Industry") ///
	value(`_combined_corr') fmt(%9.2f)

project, creates("${root}/results/paper numbers/`category'/Affinity Solutions Data vs MRTS by Industry.yaml")
